package com.leoman.dao;

import com.leoman.entity.AppUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * 普通用户
 * Created by 涂奕恒 on 2016/10/18.
 */
public interface AppUserDao extends JpaRepository<AppUser, Integer>, JpaSpecificationExecutor<AppUser> {

    @Query("select a from AppUser a where a.mobile = ?1")
    AppUser findOneByMobile(String mobile);

    @Query("select a from AppUser a where a.openId = ?1")
    AppUser findOneByOpenId(String openId);

    @Query("select a from AppUser a where a.createTime >= ?1")
    List<AppUser> findListByCreateTime(Long createTime);

    @Query("select a from AppUser a where a.sex = ?1")
    List<AppUser> findListBySex(Integer sex);

    @Query("select a from AppUser a where a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?1)")
    List<AppUser> findListByCompanyUserId(Integer companyUserId);

    @Query("select a from AppUser a where a.sex = ?1 and a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?2)")
    List<AppUser> findListBySexAndCompanyUserId(Integer sex, Integer companyUserId);

    @Query("select a from AppUser a where a.age <= ?1 and a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?2)")
    List<AppUser> findListByAgeAndCompanyUserId(Integer minAge, Integer companyUserId);

    @Query("select a from AppUser a where a.age > ?1 and a.age <= ?2 and a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?3)")
    List<AppUser> findListByAgeAndCompanyUserId(Integer minAge, Integer maxAge, Integer companyUserId);

    @Query("select a from AppUser a where a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?1) group by a.region.city.province.id order by count(a) desc")
    List<AppUser> findProvinceListByCompanyUserIdOrderByDesc(Integer companyUserId);

    @Query("select a from AppUser a where a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?1) group by a.region.city.id order by count(a) desc")
    List<AppUser> findCityListByCompanyUserIdOrderByDesc(Integer companyUserId);

    @Query("select a from AppUser a where a.region.city.province.id = ?1 and a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?2)")
    List<AppUser> findListByProvinceIdAndCompanyUserId(Integer provinceId, Integer companyUserId);

    @Query("select a from AppUser a where a.region.city.id = ?1 and a.id in (select b.userResume.appUser.id from VideoResume b where b.videoRecord.video.companyUser.id = ?2)")
    List<AppUser> findListByCityIdAndCompanyUserId(Integer cityId, Integer companyUserId);

    @Query("select a from AppUser a where a.age <= ?1")
    List<AppUser> findListByAge(Integer minAge);

    @Query("select a from AppUser a where a.age > ?1 and a.age <= ?2")
    List<AppUser> findListByAge(Integer minAge, Integer maxAge);

    @Query("select a from AppUser a group by a.region.city.province.id order by count(a) desc")
    List<AppUser> findProvinceListOrderByDesc();

    @Query("select a from AppUser a group by a.region.city.id order by count(a) desc")
    List<AppUser> findCityListOrderByDesc();

    @Query("select a from AppUser a where a.region.city.province.id = ?1")
    List<AppUser> findListByProvinceId(Integer provinceId);

    @Query("select a from AppUser a where a.region.city.id = ?1")
    List<AppUser> findListByCityId(Integer cityId);
}